﻿using System;
using System.Collections.Generic;
using System.Text;
using WordService.Data.IDAL;
using System.Data.SqlClient;
using WordService.DBUtil;
using System.Data;
using WordService.Model;

namespace WordService.Data.DAL
{
    public sealed class KeywordDAL : IKeywordDAL
    {
        private const String KEYWORD_INSERT = @""
            + "INSERT INTO BAS_KEYWORD (NAME,COUNT,RIGHTRATE,ARTICLE_ID,TOTALCOUNT) VALUES (@Name,@Count,@RightRate,@ArticleID,@TotalCount);"
            + "SELECT @@IDENTITY AS ID;";
        private const String KEYWORD_QUERY = @"SELECT * FROM BAS_KEYWORD";
        /// <summary>
        /// 
        /// </summary>
        /// <param name="name"></param>
        /// <param name="count"></param>
        /// <param name="rightRate"></param>
        /// <param name="articleID"></param>
        /// <param name="totalCount"></param>
        /// <returns></returns>
        public Int64 InsertKeyword(String name, Int64 count, decimal rightRate, Int64 articleID, Int64 totalCount)
        {
            // 初始化参数
            SqlParameter[] keywordParms = new SqlParameter[]{
                new SqlParameter("@Name",name),
                new SqlParameter("@Count",count),
                new SqlParameter("@RightRate",rightRate),
                new SqlParameter("@ArticleID",articleID),
                new SqlParameter("@TotalCount",totalCount)
            };
            // 初始化连接
            Int64 ID = 0L;
            using (SqlDataReader dr = DBUtility.ExecuteReader(
                DBUtility.ConnectionStr,
                CommandType.Text,
                KEYWORD_INSERT,
                keywordParms))
            {
                while (dr.Read())
                {
                    ID = Convert.ToInt64(dr["ID"]);
                }
            }
            // 获取返回ID
            return ID;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="authorName"></param>
        /// <param name="insName"></param>
        /// <param name="frequency"></param>
        /// <param name="year"></param>
        /// <returns></returns>
        public IList<Model.Keyword> QueryByCondition(string authorName, string insName, long frequency, string year)
        {
            #region 动态生成SQL语句
            IList<String> sqlList = new List<String>();
            if (year != null && !year.Trim().Equals(String.Empty))
            {
                sqlList.Add("(ARTICLE_ID IN (SELECT ARTICLE_ID FROM BAS_ARTICLE WHERE PUBLISHDATE LIKE '%" + year + "%'))");
            }
            if (frequency != 0)
            {
                sqlList.Add("(TOTALCOUNT > " + frequency + ")");
            }
            if (authorName != null && !authorName.Trim().Equals(String.Empty))
            {
                sqlList.Add("(ARTICLE_ID IN (SELECT ARTICLE_ID FROM BAS_AUTHOR WHERE NAME LIKE '%" + authorName + "%'))");
            }
            if (insName != null && !insName.Trim().Equals(String.Empty))
            {
                sqlList.Add("(ARTICLE_ID IN (SELECT ARTICLE_ID FROM BAS_INSTITUTION WHERE NAME LIKE '%" + insName + "%'))");
            }
            StringBuilder builder = new StringBuilder();
            builder.Append(KEYWORD_QUERY);
            builder.Append(DBUtility.GenerateSQLCondition(sqlList));
            #endregion

            IList<Keyword> keywordList = new List<Keyword>();
            using (SqlDataReader dr = DBUtility.ExecuteReader(
                DBUtility.ConnectionStr,
                CommandType.Text,
                builder.ToString()))
            {
                while (dr.Read())
                {
                    Keyword keyword = new Keyword();
                    keyword.ID = Convert.ToInt64(dr["ID"]);
                    keyword.Name = dr["NAME"].ToString();
                    keyword.Count = Convert.ToInt64(dr["COUNT"]);
                    keyword.RightRate = Convert.ToDouble(dr["RIGHTRATE"]);
                    keyword.TotalCount = Convert.ToInt64(dr["TOTALCOUNT"]);
                    // 未对ArticleID设置值
                    Article article = new Article();
                    article.ID = Convert.ToInt64(dr["ARTICLE_ID"]);
                    keyword.Article = article;
                    keywordList.Add(keyword);
                }
            }
            return keywordList;
        }
    }
}
